/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.Customer;
import ModelLayer.Invoice;
import ModelLayer.SalesOrder;
import java.sql.*;

/**
 *
 * @author Henrik-iMac
 */
public class DBSalesOrder implements IFDBSalesOrder{
    private Connection con;
    public DBSalesOrder()
    {
        con = DbConnection.getInstance().getDBcon();
    }
    //get one supplier
    public SalesOrder findBySalesOrderId(int id, boolean retrieveAssociation){
        String wClause = "id = " + id;
        System.out.println("Search salesorder: " + wClause);
        return singleWhere(wClause, retrieveAssociation);
    }

    //insert a new supplier
    public int insertSalesOrder(SalesOrder salesOrder){
        int nextId = GetMax.getMaxId("Select max(id) from SalesOrder");
        nextId = nextId + 1;
        System.out.println("next id = " +  nextId);

        int rc =-1;

        String query ="INSERT INTO SalesOrder(id, date, deliveryStatus, deliveryDate, invoiceId, customerId) VALUES("+
                nextId + ", '" +
                salesOrder.getDate() + "', '" +
                salesOrder.getDeliveryStatus() +"', '" +
                salesOrder.getDeliverDate() + "', " +
                salesOrder.getInvoiceId().getInvoiceNo() + ", " +
                salesOrder.getCustomerId().getId() + ")";
        System.out.println("Insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception e) {
            System.out.println("Insert exception in salesorder db: "+e);
        }
        return rc;
    }
    //update information about a supplier
    public int updateSalesOrder(SalesOrder salesOrder){
        int rc = -1;
        SalesOrder sObj = salesOrder;

        String query = "UPDATE SalesOrder SET " +
                "id = " + sObj.getId() + ", " +
                "date = '" + sObj.getDate() + "', "+
                "deliveryStatus = '" + sObj.getDeliveryStatus() + "', "+
                "deliveryDate = '" + sObj.getDeliverDate() + "', " +
                "invoiceId = " + sObj.getInvoiceId().getInvoiceNo() + ", "+
                "customerId = " + sObj.getCustomerId().getId() +
                " WHERE id = " + sObj.getId();
        System.out.println("Update query: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception e) {
            System.out.println("Update exception in salesorder db: "+e);
        }
        return rc;
    }
    //delete supplier
    public int deleteSalesOrder(int id){
        int rc = -1;
        String query="DELETE FROM SalesOrder WHERE id = "+id;
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Delete exception in SalesOrder db: "+e);
        }
        return rc;
    }

    //Singlewhere is used when we only select one supplier
    private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        SalesOrder sObj = new SalesOrder();
        sObj.setId(0);
        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                sObj = buildSalesOrder(results);
                stmt.close();
                if(retrieveAssociation)
                {
                    IFDBInvoice dbInv = new DBInvoice();
                    Invoice invObj = dbInv.findInvoiceById(sObj.getId());
                    System.out.println("Invoice id is selected");
                    sObj.setInvoiceId(invObj);

                    IFDBCustomer dbCust = new DBCustomer();
                    Customer cusObj = dbCust.findCustomerById(sObj.getCustomerId().getId(), false);
                    System.out.println("Customer id is selected");
                    sObj.setCustomerId(cusObj);
                }
                else
                {
                    sObj.setInvoiceId(null);
                    sObj.setCustomerId(null);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return sObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM SalesOrder";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private SalesOrder buildSalesOrder(ResultSet results){
        SalesOrder sObj = new SalesOrder();
        try {
            sObj.setId(results.getInt(1));
            sObj.setDate(results.getString(2));
            sObj.setDeliveryStatus(results.getString(3));
            sObj.setDeliverDate(results.getString(4));
            sObj.setInvoiceId(new Invoice(results.getInt(5)));
            sObj.setCustomerId(new Customer(results.getInt(6)));
        }
        catch (Exception e) {
            System.out.println("error in building the supplier object");
        }
        return sObj;
    }
}
